﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;

namespace ShopeeWebApi.AmazonBase
{
    public class AmazonBase
    {     
        public static XSSFWorkbook GetXSSFWorkbook()
        {
            string fileName = "Flat.File.Baby.ae.xlsm";
            Stream stream = File.OpenRead(@"D:\Flat.File.Baby.ae.xlsm");
            string _ext = fileName.Substring(fileName.LastIndexOf("."), fileName.Length - fileName.LastIndexOf(".")).ToLower();
            if (_ext == ".xlsx" || _ext == ".xlsm")
            {
                return new XSSFWorkbook(stream);
            }
            return null;
        }


        public static IWorkbook GetExcelSheet()
        {
            string fileName = "Flat.File.Baby.ae.xlsm";
            Stream stream = File.OpenRead(@"D:\Flat.File.Baby.ae.xlsm");
            IWorkbook workbook = null;
            string _ext = fileName.Substring(fileName.LastIndexOf("."), fileName.Length - fileName.LastIndexOf(".")).ToLower();
            if (_ext == ".xlsx" || _ext == ".xlsm")
            {
                workbook = new XSSFWorkbook(stream);
            }
            else
            {
                workbook = new HSSFWorkbook(stream);
            }
            return workbook;
        }

        public static string RegFormula(string str)
        {
            if (str.Contains("LEN"))
            {
                string pattern = @"\w{1,}\d{1,}";
                Regex r = new Regex(pattern);
                Match m = r.Match(str);
                if (m.Success)
                {
                    str = str.Replace(m.Value, "");
                }
            }
            else
            {
                string pattern = @"\$\w{1,}\d{1,}|\$\w{1,}\$\d{1,}|\w{1,}\d{1,}";
                Regex r = new Regex(pattern);
                MatchCollection mCollection = r.Matches(str);
                foreach (var collection in mCollection)
                {
                    str = str.Replace(collection.ToString(), "");
                }
            }
            return str.Trim();
        }
    }
}
